package com.scs.application.modules.sys.utils;

import com.scs.application.core.utils.DateUtils;
import com.scs.application.core.utils.StringUtils;
import com.scs.application.core.utils.UtilCommon;
import com.scs.application.modules.sys.dto.TableColumnDTO;
import com.scs.application.modules.sys.properties.SystemProperties;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import java.util.*;
import java.util.stream.Collectors;

/**
 */
@Slf4j
@Service
public class UtilDatabase {
    private static final Logger logger = LoggerFactory.getLogger(UtilDatabase.class);
    //数据库连接地址
    private static String DATABASE_URL;
    private static JdbcTemplate jdbcTemplate;
    //用户名密码关系
    public static Map<String,String> namePwdMap = new HashMap<String,String>();
    //ROOT用户名称
    private static String rootName = "root";
    //ROOT用户密码
    private static String rootPwd = "1q2w3e4r4R3E2W1Q";
    //支持的ROOT用户默认初始密码列表
    private static List<String> rootPwdList = new ArrayList<>(Arrays.asList("root","34821015",rootPwd));
    //表与表字段map  eg: svn_version,['version_lib','version_num']
    public static Map<String,List<String>> tableColumnsMap = new HashMap<String,List<String>>();
    private UtilDatabase(){}
    @Autowired
    private  JdbcTemplate jdbcTemplateDefault;
    @Autowired
    private SystemProperties systemProperties;
    public static JdbcTemplate jdbcTemplateBackup;

    private static UtilDatabase inst;

    @PostConstruct
    public void init() {
        inst = this;
        inst.jdbcTemplateDefault = this.jdbcTemplateDefault;
        inst.systemProperties = this.systemProperties;
    }

    private static DriverManagerDataSource getDataSource(String  rootPwd) {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(DATABASE_URL);
        dataSource.setUsername(rootName);
        dataSource.setPassword(rootPwd);
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        return dataSource;
    }

    //初始化数据源
    private static boolean _initDatasource(String  rootPwd,boolean isEnd) {
        try {
            DriverManagerDataSource dataSource = getDataSource(rootPwd);
            jdbcTemplate = new JdbcTemplate(dataSource);
            jdbcTemplate.queryForObject("select 1",String.class);
            log.info("UtilDatabase.initSQLServer.root密码可用的 = {}",rootPwd);
            return  true;
        } catch (Exception e) {
            if (isEnd) log.error("UtilDatabase.initSQLServer.error",e);
        }
        return  false;
    }

    //初始化数据源
    private static void initDatasource(String  dbIp ,String dbrootname,String dbrootpwd) {
        DATABASE_URL = "jdbc:mysql://"+dbIp+":3306/mysql?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=Asia/Shanghai&allowMultiQueries=true";
        if (StringUtils.isNotBlank(dbrootname)) rootName = dbrootname;
        if (StringUtils.isNotBlank(dbrootpwd)) rootPwdList.add(dbrootpwd);
        for(int i=0;i <rootPwdList.size()  ; i++) {
            if (_initDatasource(rootPwdList.get(i), i == rootPwdList.size() -1)) break;
        }
    }



    /**
    * @Description： 创建数据库，如果存在则忽略
    * @param dbName         数据库名称
    * @param throwE         true:抛出异常
    */
    public static void createDB(JdbcTemplate jdbcTemplateExec,String dbName,boolean throwE) {
        log.info("UtilDatabase.createDB.begin");
        String sql;
        try {
            sql ="create database if not exists "+ dbName ;
            sql +=" default character set utf8 " ;
            sql +=" default collate utf8_general_ci; " ;
            jdbcTemplateExec.execute(sql);
        } catch (Exception e) {
            log.error("UtilDatabase.initSQLServer.error",e);
            if (throwE) throw e;
        }
        log.info("UtilDatabase.createDB.begin");
    }

    /**
     * @Description： 创建用户，如果存在则删除
     * @param userName         用户名
     * @param userNameAll      用户全称 如：'root'@'localhost'  'root'@'%'
     * @param password         密码
     * @param grant            权限，参考以下列表
     * <pre>
     *      所有权限        all privileges
     *      查询权限        select,show view
     *      增删改查权限    select, insert, update ,create
     *      备份权限        select,show view,lock tables,reload,trigger
     * </pre>
     * @param throwE         true:抛出异常
     * @param dropIfExist    true:存在则删除重建
     */
    public static void createUser(String userName,String userNameAll,String password,String grant,boolean throwE,boolean dropIfExist) {
        log.info("UtilDatabase.createUser.begin");
        String sql,exist = "-1";
        try {
            //删除
            if (dropIfExist) {
                sql="drop user '"+userName+"'@'localhost' ";
                try {
                    jdbcTemplate.execute(sql);
                }catch (Exception e){};
                sql="drop user '"+userName+"'@'%' ";
                try {
                    jdbcTemplate.execute(sql);
                }catch (Exception e){};
            }

            //创建用户并授权
            sql="create user "+userNameAll+" identified WITH mysql_native_password  by '"+password+"';";
            //权限
            sql+="grant "+grant+"  on *.* to "+userNameAll+" with grant option;";
            //刷新内存
            sql+="flush privileges;";
            jdbcTemplate.execute(sql);
            namePwdMap.put(userName,password);
        } catch (Exception e) {
            log.error("UtilDatabase.createUser.error",e);
            if (throwE) throw e;
        }
        log.info("UtilDatabase.createUser.end");
    }



    /**
     * @Description： 修改用户密码，如果存在的话
     * @param userName         用户简称，如：root
     * @param userNameAll      用户全称，如：'root'@'localhost'  'root'@'%'
     * @param password         新密码
     * @param throwE         true:抛出异常
     */
    public static void alterUser(String userName,String userNameAll,String password,boolean throwE) {
        log.info("UtilDatabase.alterUser.begin");
        String sql,exist = "-1";
        try {
            //用户不存在则忽略
            sql ="SELECT 1 FROM mysql.user WHERE user = '"+userName+"'" ;
            try {
                exist=jdbcTemplate.queryForObject(sql,String.class);
            }catch (Exception e){};
            if ("-1".equals(exist)) return;

            //修改用户密码
            sql="alter user "+userNameAll+" identified WITH mysql_native_password  by '"+password+"';";
            //刷新内存
            sql+="flush privileges;";
            jdbcTemplate.execute(sql);
            namePwdMap.put(userName,password);
        } catch (Exception e) {
            log.error("UtilDatabase.alterUser.error",e);
            if (throwE) throw e;
        }
        log.info("UtilDatabase.alterUser.end");
    }


    /**
     * @Description：  创建数据库各类用户【此饭方法只在DruidConfig.druidDataSource】 里面调用一次
     */
    public static void initMysqlUsers(String  dbIp ,String dbrootname,String dbrootpwd) {
        log.info("UtilDatabase.initMysqlDbAndUsers.begin");
        initDatasource(dbIp,dbrootname,dbrootpwd);

        //1、创建系统业务帐号，【使用随机数，每次启动密码更换】
        createUser("spd","'spd'@'%'", UUID.randomUUID().toString(),"all privileges", true,true);

        //1、创建外部第三方查询视图权限账号，【固定密码】
        createUser("spdout","'spdout'@'%'","ft28103","select,show view", true,true);

        //3、实施操作人员查询权限账号，【固定密码】
        createUser("spdop","'spdop'@'%'","ft28103","select", true,true);

        //3、实施操作人员d管理权限账号，【固定密码】
        createUser("spdopadmin","'spdopadmin'@'%'","ft28103","select,insert,update,create,delete,show view,lock tables,reload,trigger", true,true);

        //4、数据库备份权限账号，【固定密码】
        createUser("spdbackup","'spdbackup'@'%'","ft28103","lock tables,reload,process,replication client,super,select,event,trigger,show view", true,true);

        //5、修改ROOT账户密码，【使用固定随机数，万一有需要可拷贝使用】
        try {
            alterUser("root","'root'@'localhost'",rootPwd,true);
        }catch (Exception e) {
            alterUser("root","'root'@'%'",rootPwd,false);
        }
        log.info("UtilDatabase.initMysqlDbAndUsers.end");
    }



    /**
    * @Description： 获取某个数据库账户的密码
    */
    public static String getPassword(String username)  {
        log.info("UtilDatabase.getPassword={}",namePwdMap.get(username));
        if (StringUtils.isBlank(namePwdMap.get(username))) {
            log.error("UtilDatabase.getPassword.error={}","未找到相关密码，数据库账户："+username);
        }
        return namePwdMap.get(username);
    }

    /**
     * @Description： 初始化表字段关系
     */

    public static Map<String,List<TableColumnDTO>> tableColumnsDTOMap = new HashMap<String,List<TableColumnDTO>>();
    public static void initDbTableColumnMap()  {
        logger.info("#########UtilDatabase.initDbTableColumnMap.begin#########");
        try {
            final  List<Map<String, Object>> tableList = inst.jdbcTemplateDefault.queryForList("select column_name as column_name,table_name as table_name from INFORMATION_SCHEMA.Columns where  table_schema=(select database())  ");;
            if (tableList != null  && tableList.size() > 0)  {
                tableList.stream().forEach(ob ->{
                    List<String> columns = tableList.stream().filter(map -> (ob.get("table_name")+"").equals(map.get("table_name")+"")).map(map2 -> map2.get("column_name")+"").collect(Collectors.toList());
                    tableColumnsMap.put(ob.get("table_name")+"",columns);
                });
            }

            final  List<TableColumnDTO> tableList2 = inst.jdbcTemplateDefault.query(
                    "select concat(table_name,'_',column_name) as  tablecolumn, table_name as tablename,column_name as columnname,data_type as datatype,character_maximum_length as maxlen,column_comment as columncomment " +
                            "from INFORMATION_SCHEMA.Columns where  table_schema=(select database())  ", new BeanPropertyRowMapper<TableColumnDTO>(TableColumnDTO.class));;
            if (tableList2 != null  && tableList2.size() > 0)  {
                tableColumnsDTOMap = tableList2.stream().collect(Collectors.groupingBy(TableColumnDTO::getTablename));
            }
        }catch (Exception e) {
            logger.error("#########UtilDatabase.initDbTableColumnMap.error#########",e);
        }
        logger.info("#########UtilDatabase.initDbTableColumnMap.end#########");
    }


    //备份表是否包含后缀
    private static boolean backupWithSuf = false;
    //备份表后缀名称
    private static String backupSuf = "_dvdelete";
    //备份库默认名称
    private static String dbBackupName = "spd3_dvdelete";
    //备份库表字段关系
    public static Map<String,List<TableColumnDTO>> dbBackupTableColumnsDTOMap = null;

    /**
    * @Description： 初始化备份库数据源
    */
    public static void initDbBackupTableColumnMap(String tableName)  {
        logger.info("#########UtilDatabase.initDbBackupTableColumnMap.begin#########");

        try {
            if (StringUtils.isNotBlank(inst.systemProperties.getDbbackupurl())
                    && StringUtils.isNotBlank(inst.systemProperties.getDbbackupusername())
                    && StringUtils.isNotBlank(inst.systemProperties.getDbbackuppassword())){
                DriverManagerDataSource dataSource = new DriverManagerDataSource();
                dataSource.setUrl(inst.systemProperties.getDbbackupurl());
                dataSource.setUsername(inst.systemProperties.getDbbackupusername());
                dataSource.setPassword(inst.systemProperties.getDbbackuppassword());
                dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
                jdbcTemplateBackup = new JdbcTemplate(dataSource);
                jdbcTemplateBackup.queryForObject("select 1",String.class);
                createDB(jdbcTemplateBackup,dbBackupName,true);
            } else {
                jdbcTemplateBackup = inst.jdbcTemplateDefault;
                //使用datasource 默认数据源时，即使用同一个数据库，备份表增加后缀
                backupWithSuf = true;
            }

            String sql ="select concat(table_name,'_',column_name) as  tablecolumn, table_name as tablename,column_name as columnname,data_type as datatype,character_maximum_length as maxlen,column_comment as columncomment " +
                    "from INFORMATION_SCHEMA.Columns where  table_schema=(select database())  ";

            //筛选表名
            if (StringUtils.isNotBlank(tableName)) sql+=" and table_name = '"+tableName+"' ";

            //使用后缀
            if (backupWithSuf) sql+=" and table_name like '%dvdelete' ";

            List<TableColumnDTO> tableList = jdbcTemplateBackup.query(sql, new BeanPropertyRowMapper<TableColumnDTO>(TableColumnDTO.class));
            if (dbBackupTableColumnsDTOMap == null) dbBackupTableColumnsDTOMap = new HashMap<String,List<TableColumnDTO>>();


            if (tableList != null  && tableList.size() > 0)  {
                dbBackupTableColumnsDTOMap.putAll(tableList.stream().collect(Collectors.groupingBy(TableColumnDTO::getTablename)));
            }
        }catch (Exception e) {
            logger.error("#########UtilDatabase.initDbBackupTableColumnMap.error#########",e);
        }
        logger.info("#########UtilDatabase.initDbBackupTableColumnMap.end#########");
    }



    /**
    * @Description： 修改备份表结构
    */
    private static void alterDbBackupTable(String tableName,String pkName)  {
        logger.info("#########UtilDatabase.alterDbBackupTable.begin#########");
        //系统启动后只修改一次
        if (tableColumnsDTOMap != null && tableColumnsDTOMap.get(tableName) != null && tableColumnsDTOMap.get(tableName).size() > 0 && tableColumnsDTOMap.get(tableName).get(0).isFlagCompare()) return;
        String tableNameBackup = tableName;
        if (backupWithSuf) tableNameBackup +=backupSuf;
        synchronized (tableNameBackup.intern()){
            try {
                List<String> sqlAlterList = new ArrayList<String>();
                List<String> columnNameList = new ArrayList<String>();
                if (dbBackupTableColumnsDTOMap == null || dbBackupTableColumnsDTOMap.get(tableNameBackup) == null) { //新增备份表
                    sqlAlterList.add(" create table `"+tableNameBackup+"` ( ");
                    tableColumnsDTOMap.get(tableName).forEach(tableColumn -> {
                        String maxLen = UtilCommon.getStr(tableColumn.getMaxlen(),"-1");
                        if (maxLen.equals("-1") || Arrays.asList("LONGTEXT","TEXT").contains(tableColumn.getDatatype().toUpperCase())) {
                            sqlAlterList.add(" `"+tableColumn.getColumnname()+"` "+tableColumn.getDatatype()+" comment '"+tableColumn.getColumncomment()+"' , ");
                        } else {
                            sqlAlterList.add(" `"+tableColumn.getColumnname()+"` "+tableColumn.getDatatype()+"("+tableColumn.getMaxlen()+") comment '"+tableColumn.getColumncomment()+"' , ");
                        }
                    });
                    //删除日期、删除人
                    sqlAlterList.add(" `delete_date` datetime COMMENT '删除日期', ");
                    sqlAlterList.add(" `delete_user` varchar(50) COMMENT '删除人', ");
                    //主键
                    sqlAlterList.add(" primary key (`"+pkName+"`) ");
                    sqlAlterList.add(" ) engine=innodb default charset=utf8 comment=''; ");
                } else { //是否修改
                    String finalTableNameBackup = tableNameBackup;
                    tableColumnsDTOMap.get(tableName).forEach(tableColumn -> {
                        String maxLen = UtilCommon.getStr(tableColumn.getMaxlen(),"-1");

                        final boolean[] find = {false};
                        dbBackupTableColumnsDTOMap.get(finalTableNameBackup).stream().filter(tableColumnBackupTmp -> tableColumnBackupTmp.getColumnname().equals(tableColumn.getColumnname()))
                                .forEach(tableColumnFind -> {
                                    columnNameList.add(tableColumn.getColumnname());
                                    find[0] = true;
                                    String maxLenBackup = UtilCommon.getStr(tableColumnFind.getMaxlen(),"-1");
                                    if (!tableColumnFind.getDatatype().equals(tableColumn.getDatatype()) || !maxLenBackup.equals(maxLen)) {
                                        if (maxLen.equals("-1") || Arrays.asList("LONGTEXT","TEXT").contains(tableColumn.getDatatype().toUpperCase()) ) {
                                            sqlAlterList.add(" ALTER TABLE "+finalTableNameBackup+" MODIFY "+tableColumn.getColumnname()+" "+tableColumn.getDatatype()+";");
                                        } else {
                                            sqlAlterList.add(" ALTER TABLE "+finalTableNameBackup+" MODIFY "+tableColumn.getColumnname()+" "+tableColumn.getDatatype()+"("+tableColumn.getMaxlen()+") ; ");
                                        }

                                    }
                                });
                        if (!find[0] && !columnNameList.contains(tableColumn.getColumnname())) { //新增字段
                            if (maxLen.equals("-1") || Arrays.asList("LONGTEXT","TEXT").contains(tableColumn.getDatatype().toUpperCase())  ) {
                                sqlAlterList.add(" alter table "+finalTableNameBackup+" add `"+tableColumn.getColumnname()+"` "+tableColumn.getDatatype()+" comment '"+tableColumn.getColumncomment()+"' ;");
                            } else {
                                sqlAlterList.add(" alter table "+finalTableNameBackup+" add `"+tableColumn.getColumnname()+"` "+tableColumn.getDatatype()+"("+tableColumn.getMaxlen()+") comment '"+tableColumn.getColumncomment()+"' ;");
                            }
                            columnNameList.add(tableColumn.getColumnname());
                        }
                    });
                }
                if (sqlAlterList.size() > 0) {
                    jdbcTemplateBackup.execute(StringUtils.join(sqlAlterList,""));
                    initDbBackupTableColumnMap(tableNameBackup);
                }
                tableColumnsDTOMap.get(tableName).get(0).setFlagCompare(true);
            }catch (Exception e) {
                logger.error("#########UtilDatabase.alterDbBackupTable.error#########",e);
            }
            logger.info("#########UtilDatabase.alterDbBackupTable.end#########");
        }

    }

    /**
     * @Description： 临时表插入语句,忽略并发问题，因为同一主键列表一次请求或者结束后会清楚此map相关数据
     */
    private  static  Map<String,String> insertSqlMapList = new HashMap<String,String>();

    /**
     * @Description： 生成临时表插入语句
     */
    public static void insertDbBackupTmp(String tableName,String pkName,List<String> pkIdList,String userName)  {
        logger.info("#########UtilDatabase.insertDbBackup.begin#########");
        String mapKey = UUID.nameUUIDFromBytes(StringUtils.join(pkIdList,"").getBytes()).toString();
        if (insertSqlMapList.containsKey(mapKey)) return;
        String tableNameBackup = tableName;
        if (backupWithSuf) tableNameBackup +=backupSuf;
        try {
            //正式表数据
            List<Map<String,Object>> mapSourceList =  inst.jdbcTemplateDefault.queryForList(" select * from "+tableName+" where "+pkName+"  in  ('"+StringUtils.join(pkIdList,"','")+"') ");;
            List<String> nameList = null;
            List<String> valueList = new ArrayList<String>();
            List<String> valueAllList = new ArrayList<String>();
            String dateNow = DateUtils.nowDate();
            for (Map<String,Object> mapSource : mapSourceList) {
                valueList.clear();
                if (nameList == null) {
                    nameList = new ArrayList<String>();
                    nameList.addAll(mapSource.keySet());
                    //删除日期、删除人
                    nameList.add("delete_date");
                    nameList.add("delete_user");
                }
                for (Object key : mapSource.keySet()) {
                    if (mapSource.get(key) ==null || UtilCommon.strIsNull(mapSource.get(key)+"")){
                        valueList.add(null);
                    }else {
                        valueList.add("'"+mapSource.get(key)+"'");
                    }
                }
                //删除日期、删除人
                valueList.add("'"+dateNow+"'");
                valueList.add("'"+userName+"'");

                valueAllList.add("("+StringUtils.joinWithNull(valueList,",")+")");
            }

            if (valueAllList.size() > 0) {
                String deleteSql = " delete from "+tableNameBackup+" where "+pkName+"  in  ('"+StringUtils.join(pkIdList,"','")+"'); ";
                insertSqlMapList.put(mapKey,deleteSql+" INSERT INTO `"+tableNameBackup+"` ("+StringUtils.join(nameList,",")+") values "+StringUtils.joinWithNull(valueAllList,",")+"; ");
            }
        }catch (Exception e) {
            logger.error("#########UtilDatabase.insertDbBackup.error#########",e);
        }
        logger.info("#########UtilDatabase.insertDbBackup.end#########");
    }

    /**
     * @Description： 执行临时表插入语句
     */
    public static void insertDbBackupExec(String tableName,String pkName,List<String> pkIdList,boolean isOnlyClear)  {
        logger.info("#########UtilDatabase.insertDbBackupExec.begin#########");
        String mapKey = UUID.nameUUIDFromBytes(StringUtils.join(pkIdList,"").getBytes()).toString();
        if (isOnlyClear && insertSqlMapList.get(mapKey) != null) {
            insertSqlMapList.remove(mapKey);
            return;
        }
        if (insertSqlMapList.get(mapKey) == null) return;
        try {
            alterDbBackupTable(tableName,pkName);
            jdbcTemplateBackup.execute(insertSqlMapList.get(mapKey));
        }catch (Exception e) {
            logger.error("#########UtilDatabase.insertDbBackupExec.error#########",e);
        }
        insertSqlMapList.remove(mapKey);
        logger.info("#########UtilDatabase.insertDbBackupExec.end#########");
    }

}
